<?php
$files = array();
include_once 'DatabaseConnection.class.php';
include_once 'Query.class.php';

if ($handle = opendir('../data/')) {


	/* This is the correct way to loop over the directory. */
	while (false !== ($file = readdir($handle))) {
		if($file == '.' || $file == '..'){
			continue;
		}
		$files[] = $file;
	}


	closedir($handle);
}


foreach($files as $file)
{

	$query = new Query("SELECT ID, name FROM tables where physicalName = '{$file}'");
	$record = $query->fetchRow();

	$tableID = $record['ID'];

	$tableName = str_replace(' ', '', $record['name']);
	$queryStr = 'SELECT * FROM fields WHERE tableID = "'.$tableID.'" ORDER BY position';

	$query = new Query($queryStr);



	$createQuery = 'CREATE TABLE IF NOT EXISTS ' . $tableName. ' (ID int(25) unsigned NOT NULL auto_increment, ';
	$fields = array();
	$lenghts = array();
	while ($record = $query->fetchRow())
	{
		$fieldName = str_replace(' ', '_', trim($record['name'])) ;
		$fields[] = $fieldName;
		$lenghts[] = $record['length'];
		$fieldLength = $record['length'];
		$createQuery = $createQuery . ' ' . $fieldName . ' varchar( ' . $fieldLength . ' ) NOT NULL,';
	}
	$createQuery .= 'PRIMARY KEY  (ID) ) ENGINE=InnoDB;';
	$inserQuery = 'INSERT INTO ' .  $tableName . ' ( ' . implode(', ', $fields) .') VALUES ';
	$inserts = array();
	
	/*$lines = file('../data/' . $file);
	
	foreach($lines as $line)
	{
		$start = 0;
		$values = array();
		foreach ($lenghts as $length)
		{
			$values[] = substr($line, $start, $length);
			$start += $length;
		}
		$inserts[] = '("' . implode('", "', $values) . '")';

	}*/

	$handle = fopen('../data/' . $file, "r") or die("Couldn't get handle");
	if ($handle) {
		while (!feof($handle)) {
			$line = fgets($handle, 4096);
			$start = 0;
		$values = array();
		foreach ($lenghts as $length)
		{
			$values[] = substr($line, $start, $length);
			$start += $length;
		}
		$inserts[] = '("' . implode('", "', $values) . '")';
		}
		fclose($handle);
	}

	$inserQuery .= implode(', ', $inserts);
	//echo $createQuery;exit;
	new Query($createQuery);
	new Query($inserQuery);
}




